//This class reads in a .CSV file and generates Insert into SQL queries and inserts the data into local 
//javabootcamp database, employee table.
//It also creates 200 insert statements, one for each employee and enters them into a text file as a backup.	

//Run as Java Application//

//*******************************************************************************************************
package ie.deloitte.cvapp.generateImportData;

import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.List;

import com.google.common.base.Splitter;
import com.google.common.io.Files;

public class ImportExcelToSQL {
	// Initialise Variables
	static int i = 1;
	static int id = 0;
	static String name;
	static String surname;
	static String profile;
	static String roleproposed;
	static String email;
	static String photo;
	static String date;
	static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	static String primarytelephone;
	static String secondarytelepohne;
	static int count = 0;

	// Main Program
	public static void main(String[] args) throws IOException, SQLException,
			ClassNotFoundException {

		// load the JDBC driver
		Class.forName("com.mysql.jdbc.Driver");

		// get a connection to the database
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/javabootcamp", "root", "admin");
		
		// empty table
//				Statement stmt = con.createStatement();
//				stmt.execute("truncate employee;");
//				System.out.println("employee table emptied.");
		
		File csvFile = new File("SampleData.csv");
		String query = "INSERT INTO "
				+ "employee(creationdate,email,name,photo,primarytelephone,profile,roleproposed,secondarytelepohne,surname)"
				+ "VALUES( 		?, 			?, 		?, 		?, 			?, 				?	, 		?, 		?,	 	?)";
		List<String> lines = Files.readLines(csvFile, Charset.defaultCharset());
		PreparedStatement pstmt = con.prepareStatement(query);

		for (String line : lines) {

			int param = 1;
			for (String value : Splitter.on(",").split(line)) {
				// Class<?> column = columnTypes[param];

				// /// go for it.
				// if(column == String.class) {
				pstmt.setString(param++, value);
			
				// }
				// if(column == int.class) {
				// ps.setInt(param++, value);
				// }
			}
			count++;
			pstmt.executeUpdate();
		}
		System.out.println(count + " rows have been added to the table employee");
	}
}
